To Load Load_StgEnds
Source VW_Ends:
Columns Tab:
Aggregate Function:
Derived Columns:
Derived Columns Text:
GETDATE()
GETDATE()
ISNULL([End_Is this a W2 employee?]) ? "Unknown" : [End_Is
this a W2 employee?]
ISNULL([End_If not a W2 employee, please provide the Subvendor
Name]) ? "Unknown" : [End_If not a W2 employee, please provide the
Subvendor Name]
ISNULL([End_Converted to FTE?]) || TRIM([End_Converted to FTE?]) ==
"" ? "Unknown" : [End_Converted to FTE?]
OLE DB Command
Mappings Tab:
To Load Load_StgRenewals:
Source Renewals:
SQL Command:
SQL Command Text:
WITH CTE AS (
select ROW_NUMBER() over (Partition by [Work Order ID]
order by [Work Order Start Date] DESC ) as StartDateRankorder,
*
from [dbo].[Renewals]
where [Work Order Start Date] is not null
)
SELECT [Work Order ID], [Revision Reason], [Revision #], [Renewal?],
[On Time?],[Work Order Start Date], [Work Order End Date],
[Previous Work Order Start Date], [Previous Work Order End Date],
[First Approval Received Date], [Work Order Create Date],
[Work Order Approved Date], [Remove?] FROM CTE
WHERE StartDateRankorder = 1
Columns Tab:
Data Conversion:
Derived Column:
Derived Column Text:
GETDATE()
GETDATE()
ISNULL([Revision Reason]) ? "Unknown" : [Revision Reason]
ISNULL([Work Order ID]) ? "Unknown" : [Work Order ID]
ISNULL([Revision #]) ? 0 : [Revision #]
ISNULL([Renewal?]) ? "Unknown" : [Renewal?]
ISNULL([On Time?]) ? "Unknown" : [On Time?]
ISNULL([Remove?]) ? "Unknown" : [Remove?]
ISNULL([Copy of Work Order Start Date]) ? "Unknown" :
[Copy of Work Order Start Date]
ISNULL([Copy of Work Order End Date]) ? "Unknown" : [Copy
of Work Order End Date]
ISNULL([Copy of Previous Work Order End Date]) ? "Unknown"
: [Copy of Previous Work Order End Date]
ISNULL([Copy of Previous Work Order Start Date]) ?
"Unknown" : [Copy of Previous Work Order Start Date]
ISNULL([Copy of First Approval Received Date]) ? "Unknown"
: [Copy of First Approval Received Date]
ISNULL([Copy of Work Order Create Date]) ? "Unknown" :
[Copy of Work Order Create Date]
ISNULL([Copy of Work Order Approved Date]) ? "Unknown" :
[Copy of Work Order Approved Date]
Conditional Split:
Conditional Split Text:
LEN([Remove?]) < 1
Derived Columns 1:
Union All:
OLE DB Destination:
Mappings Tab:
Conditional Split transformation
https://www.sqlshack.com/ssis-conditional-split-transform-overview/